Relational Searching

Relational searching can be performed on database fields that have been defined with the Sort Key flag. These are often fields containing dates, document IDs, or similar content that is useful when sorted. Columns in the case table are sortable if the Sort Key flag is set.

A relational search differs from a full-text search in that the field contents are compared to the search term as a single unit for an exact match rather than comparing the field contents one word at a time. A relational search looks at database records themselves; a full-text search examines an index file that contains pointers to the records.

For example, if an AUTHOR field is defined with the Sort Key flag, either a full-text or relational search can be performed, as detailed in the following table.

Type of Search	Description
Full-text	Example: [author] CO john smith Results: Finds all records that contain both “john” and “smith” anywhere in the author field. In full-text searches, if the operation code is missing, “CO” is assumed.
Relational	Example: [author] = john smith Results: Finds only those records in which the entire content of the author field is equal to “john smith.” In relational searches, if the operation code is missing, “=” is assumed.

Type of Search

Description

Full-text

Example: [author] CO john smith

Results: Finds all records that contain both “john” and “smith” anywhere in the author field.

In full-text searches, if the operation code is missing, “CO” is assumed.

Relational

Example: [author] = john smith

Results: Finds only those records in which the entire content of the author field is equal to “john smith.”

In relational searches, if the operation code is missing, “=” is assumed.

Relational Operators

The following operators can be used in relational search expressions.

Note: All relational operators must be preceded by a field name enclosed in square brackets and should also have a space before/after the code. For example: [author] = john smith

Operator	Syntax	Descriptions
Equal	= or EQ	Example:[creation date] = 11/11/2001 Results: Results will include all records with the specified date.
Greater than	> or GT	Example:[doc id] > 200 Results: Results will include records with identification numbers from 201 on.
Greater than or equal to	>= or GE	Example:[doc id] ge 200 Results: Results will include records with identification numbers from 200 on.
Less than	< or LT	Example: [doc id] < 200 Results: Results will include records with identification numbers from 1 - 199.
Less than or equal to	<= or LE	Example:[doc id] le 200 Results: Results will include records with identification numbers from 1 - 200.
Not equal to	<> or NE	Example:[author] ne john smith Results: Results will include records for which the author is not “John Smith.”
Within range of	~ or RG	Operator must be followed by two search terms separated by a hyphen. Example:[date] ~ 01/01/2001 - 12/31/2005 Results: Results will include records with dates ranging from January 1, 2001 through December 31, 2005.
Empty field	NULL	An empty field can be specified using the reserved word “NULL.” Example:[date] = NULL Results: Results will include all records that do not contain a date in the field.